package code20_jdbc.学生管理系统;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import code20_jdbc.JdbcUtil;

public class Test {
	
	private static StudentTemplate template = new StudentTemplate();
	
	public static void main(String[] args) {
		System.out.println(selectAll());
		
//		System.out.println(selectById(1));
		
		System.out.println(selectByCondition("a", 10, 40));
		
//		Student stu = new Student();
//		stu.setName("yyy");
//		stu.setAge(20);
//		stu.setHeight(190.5);
//		stu.setBirthday(new Date());
//		stu.setClazz(new Clazz(2));
//		insert(stu);
		
//		deleteById(1);
	}
	
	// 1. 查询所有学生
	public static List<Student> selectAll(){
		String sql = new StringBuffer()
			.append(" select ")
			.append(" 	s.id, ")
			.append(" 	s.name, ")
			.append(" 	s.age, ")
			.append(" 	s.height, ")
			.append(" 	s.birthday, ")
			.append(" 	c.cid, ")
			.append(" 	c.cname, ")
			.append(" 	c.room ")
			.append(" from ")
			.append(" 	t_student s,t_class c ")
			.append(" where ")
			.append(" 	s.cid=c.cid ")
			.toString();
		return template.query(sql);
	}
	
	// 2. 根据学号查询学生
	public static Student selectById(int id){
		String sql = new StringBuffer()
		.append(" select ")
		.append(" 	s.id, ")
		.append(" 	s.name, ")
		.append(" 	s.age, ")
		.append(" 	s.height, ")
		.append(" 	s.birthday, ")
		.append(" 	c.cid, ")
		.append(" 	c.cname, ")
		.append(" 	c.room ")
		.append(" from ")
		.append(" 	t_student s,t_class c ")
		.append(" where ")
		.append(" 	s.cid=c.cid ")
		.append(" 		and ")
		.append(" 	s.id=? ")
		.toString();
		List<Student> list = template.query(sql, id);
		return list.isEmpty() ? null : list.get(0);
	}
	
	// 3. 根据姓名和年龄范围查询学生
	public static List<Student> selectByCondition(String name,int minAge,int maxAge){
		String sql = new StringBuffer()
		.append(" select ")
		.append(" 	s.id, ")
		.append(" 	s.name, ")
		.append(" 	s.age, ")
		.append(" 	s.height, ")
		.append(" 	s.birthday, ")
		.append(" 	c.cid, ")
		.append(" 	c.cname, ")
		.append(" 	c.room ")
		.append(" from ")
		.append(" 	t_student s,t_class c ")
		.append(" where ")
		.append(" 	s.cid=c.cid ")
		.append(" 		and ")
		.append(" 	s.name like ? ")
		.append(" 		and ")
		.append(" 	s.age between ? and ? ")
		.toString();
		return template.query(sql, "%"+name+"%", minAge, maxAge);
	}
	
	// 4. 添加学生
	public static void insert(Student stu){
		String sql = new StringBuffer()
			.append(" insert into t_student ")
			.append(" 	(name,age,height,birthday,cid) ")
			.append(" values ")
			.append(" 	(?,?,?,?,?) ")
			.toString();
		template.update(sql, stu.getName(),stu.getAge(),stu.getHeight(),stu.getBirthday(),stu.getClazz().getCid());
	}
	
	// 5. 修改学生
	public static void update(Student stu){
		String sql = new StringBuffer()
			.append(" update ")
			.append(" 	t_student ")
			.append(" set ")
			.append(" 	name=?,age=?,height=?,birthday=? ")
			.append(" where ")
			.append(" 	id=? ")
			.toString();
		template.update(sql, stu.getName(),stu.getAge(),stu.getHeight(),stu.getBirthday(),stu.getId());
	}
	
	// 6. 根据学号删除学生
	public static void deleteById(int id){
		String sql = new StringBuffer()
			.append(" delete from ")
			.append(" 	t_student ")
			.append(" where ")
			.append(" 	id=? ")
			.toString();
		template.update(sql, id);
	}
}
